﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using QuanLyRapChieuPhim.DTO;
using System.ComponentModel;

namespace QuanLyRapChieuPhim.DAO
{
    class TaiKhoanDAO
    {
        DataProvider dp = new DataProvider();

        public BindingList<TaiKhoanDTO> GetList()
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                dt = dp.SqlExcuteQuery("uproc_GetList_TaiKhoan", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                    TaiKhoan.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetList_QLNhanVien()
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                dt = dp.SqlExcuteQuery("uproc_GetList_TaiKhoan_QLNhanVien", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenNhanVien"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());
                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetList_QLNhanVien_DaXoa()
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                dt = dp.SqlExcuteQuery("uproc_GetList_TaiKhoan_QLNhanVien_DaXoa", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenNhanVien"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());
                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetListListLikeName_NhanVien_QL(string _Ten)
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                    { 
                        new SqlParameter("@Ten", _Ten) 
                    };
                dt = dp.SqlExcuteQuery("uproc_GetListLikeName_TaiKhoan_NhanVien_QL", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenNhanVien"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());

                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetListListLikeName_NhanVien_QL_DaXoa(string _Ten)
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                    { 
                        new SqlParameter("@Ten", _Ten) 
                    };
                dt = dp.SqlExcuteQuery("uproc_GetListLikeName_TaiKhoan_NhanVien_QL_DaXoa", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenNhanVien"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());

                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetList_QLKhachHang()
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                dt = dp.SqlExcuteQuery("uproc_GetList_TaiKhoan_QLKhachHang", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenKhachHang"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());

                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetList_QLKhachHang_DaXoa()
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                dt = dp.SqlExcuteQuery("uproc_GetList_TaiKhoan_QLKhachHang_DaXoa", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenKhachHang"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());

                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetListListLikeName_KhachHang_QL(string _Ten)
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                    { 
                        new SqlParameter("@Ten", _Ten) 
                    };
                dt = dp.SqlExcuteQuery("uproc_GetListLikeName_TaiKhoan_KhachHang_QL", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenKhachHang"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());

                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public BindingList<TaiKhoanDTO> GetListListLikeName_KhachHang_QL_DaXoa(string _Ten)
        {

            DataTable dt = new DataTable();
            BindingList<TaiKhoanDTO> ds = new BindingList<TaiKhoanDTO>();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                    { 
                        new SqlParameter("@Ten", _Ten) 
                    };
                dt = dp.SqlExcuteQuery("uproc_GetListLikeName_TaiKhoan_KhachHang_QL_DaXoa", null);
                dp.CloseConnect();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                    TaiKhoan.HoTenNguoiDung = row["tenKhachHang"].ToString();
                    TaiKhoan.TenLoaiTaiKhoan = row["tenChucVu"].ToString();
                    TaiKhoan.DaXoa = int.Parse(row["DaXoa"].ToString());

                    ds.Add(TaiKhoan);
                }
                return ds;
            }
            return null;
        }

        public TaiKhoanDTO GetFromName(string _Ten)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@Ten", _Ten) };
                dt = dp.SqlExcuteQuery("uproc_GetFromName", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                }
                return TaiKhoan;
            }
            return null;
        }

        public TaiKhoanDTO GetFromId(int _Ma)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@Ma", _Ma) };
                dt = dp.SqlExcuteQuery("uproc_GetFromID", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                }
                return TaiKhoan;
            }
            return null;
        }

        public TaiKhoanDTO GetFromId_KhachHang(int _Ma)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@Ma", _Ma) };
                dt = dp.SqlExcuteQuery("uproc_GetFromID_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {

                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                }
                return TaiKhoan;
            }
            return null;
        }

        public TaiKhoanDTO GetFromId_NhanVien(int _Ma)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@Ma", _Ma) };
                dt = dp.SqlExcuteQuery("uproc_GetFromID_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO TaiKhoan = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {

                    TaiKhoan.MaTaiKhoan = int.Parse(row["maTaiKhoan"].ToString());
                    TaiKhoan.TenDangNhap = row["tenDangNhap"].ToString();
                    TaiKhoan.MatKhau = row["matKhau"].ToString();
                    TaiKhoan.MaLoaiTaiKhoan = int.Parse(row["maLoaiTaiKhoan"].ToString());
                    TaiKhoan.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                }
                return TaiKhoan;
            }
            return null;
        }

        public int GetIDFromIDTTDat(int _Ma)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@MaTTDatVe", _Ma) };
                dt = dp.SqlExcuteQuery("uproc_IDTaiKhoanFromIDTTDatVe", sqlParameter);
                dp.CloseConnect();
                SuatChieuDTO SuatChieu = new SuatChieuDTO();
                if (dt.Rows.Count > 0)
                {
                    return int.Parse(dt.Rows[0][0].ToString());
                }
                dt = dp.SqlExcuteQuery("uproc_GetFirtID_TaiKhoan_KhachHang", null);
                dp.CloseConnect();
                int kq = int.Parse(dt.Rows[0][0].ToString());
                return kq;
            }
            return -1;
        }

        public int GetIDFromIDNhanVien(int _Ma)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@Ma", _Ma) };
                dt = dp.SqlExcuteQuery("uproc_GetIDTaiKhoanFromID_NhanVien", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO DTO = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    DTO.MaTaiKhoan = int.Parse(row["MaTaiKhoan"].ToString());
                }
                if (dt.Rows.Count > 0)
                    return int.Parse(dt.Rows[0][0].ToString());
                return 0;
            }
            return -1;
        }

        public int CheckLogin(string _TenDangNhap, string _MatKhau)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@TenDangNhap", _TenDangNhap) ,
                    new SqlParameter("@MatKhau", _MatKhau) 
                };
                dt = dp.SqlExcuteQuery("uproc_CheckLogin", sqlParameter);
                dp.CloseConnect();
                if (dt.Rows.Count > 0)
                    return int.Parse(dt.Rows[0][0].ToString());
                return 0;
            }
            return -1;
        }

        public int GetIDFromIDKhachHang(int _Ma)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@Ma", _Ma) };
                dt = dp.SqlExcuteQuery("uproc_GetIDTaiKhoanFromID_KhachHang", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO DTO = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    DTO.MaTaiKhoan = int.Parse(row["MaTaiKhoan"].ToString());
                }
                if (dt.Rows.Count > 0)
                    return int.Parse(dt.Rows[0][0].ToString());
                return 0;
            }
            return -1;
        }

        public int GetIDFromName(string _TenDangNhap)
        {
            DataTable dt = new DataTable();
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@TenDangNhap", _TenDangNhap) };
                dt = dp.SqlExcuteQuery("uproc_CheckExists_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                SuatChieuDTO SuatChieu = new SuatChieuDTO();
                if (dt.Rows.Count > 0)
                    return int.Parse(dt.Rows[0][0].ToString());
                return 0;
            }
            return -1;
        }

        public bool Delete(int _Ma)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@Ma", _Ma) 
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Delete_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool UnDelete(int _Ma)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@Ma", _Ma) 
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_UnDelete_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool Add_NhanVien(TaiKhoanDTO _TK)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@TenDangNhap", _TK.TenDangNhap), 
                    new SqlParameter("@MatKhau", _TK.MatKhau), 
                    new SqlParameter("@MaLoaiTaiKhoan", _TK.MaLoaiTaiKhoan), 
                    new SqlParameter("@MaNhanVien", _TK.MaNhanVien)
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Add_TaiKhoan_NhanVien", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;

        }

        public bool Add_KhachHang(TaiKhoanDTO _TK)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@TenDangNhap", _TK.TenDangNhap), 
                    new SqlParameter("@MatKhau", _TK.MatKhau), 
                    new SqlParameter("@MaLoaiTaiKhoan", _TK.MaLoaiTaiKhoan), 
                    new SqlParameter("@MaKhachHang", _TK.MaKhachHang) 
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Add_TaiKhoan_KhachHang", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;

        }

        public bool Update_NhanVien(TaiKhoanDTO _TK)
        {
            bool kq;
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@MaTaiKhoan", _TK.MaTaiKhoan), 
                    new SqlParameter("@TenDangNhap", _TK.TenDangNhap), 
                    new SqlParameter("@MatKhau", _TK.MatKhau), 
                    new SqlParameter("@MaLoaiTaiKhoan", _TK.MaLoaiTaiKhoan), 
                    new SqlParameter("@MaNhanVien", _TK.MaNhanVien)
                };
                kq = dp.SqlExcuteNonQuery("uproc_Update_TaiKhoan_NhanVien", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool Update_KhachHang(TaiKhoanDTO _TK)
        {
            bool kq;
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@MaTaiKhoan", _TK.MaTaiKhoan), 
                    new SqlParameter("@TenDangNhap", _TK.TenDangNhap), 
                    new SqlParameter("@MatKhau", _TK.MatKhau), 
                    new SqlParameter("@MaLoaiTaiKhoan", _TK.MaLoaiTaiKhoan),
                    new SqlParameter("@MaKhachHang", _TK.MaKhachHang) 
                };
                kq = dp.SqlExcuteNonQuery("uproc_Update_TaiKhoan_KhachHang", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }
    
    }
}
